package EPM;

import com.taskadapter.redmineapi.*;
import com.taskadapter.redmineapi.bean.Issue;
import com.taskadapter.redmineapi.internal.Transport;
import oracle.sql.BLOB;

import java.io.File;
import java.io.InputStream;
import java.nio.file.Files;
import java.sql.*;
import java.util.*;
import java.util.Date;

public class Redmine2EPM {
//    public static void main(String[] args) {
//        Redmine2EPM redmine2EPM = new Redmine2EPM();
//        redmine2EPM.back2EPM();
//    }

    public void back2EPM() {
        Map<String, String> status_map = new HashMap<>();
        status_map.put("0", "37");// 37新建
        status_map.put("1", "38");// 38进行中
        status_map.put("2", "37");
        status_map.put("3", "38");
        status_map.put("4", "38");
        status_map.put("5", "38");
        status_map.put("6", "38");
        status_map.put("7", "39");// 39已解决
        status_map.put("8", "40");// 40已关闭
        status_map.put("10", "38");
        status_map.put("11", "41");// 41已挂起

        RedmineManager redmineManager = RedmineManagerFactory.createWithApiKey("http://192.168.8.10:8001/", "45b5fc0e9fecba9fa2593360d83eafa0a79c3a66");
        Transport transport = redmineManager.getTransport();

        Connection oracle = null;
        try {
            oracle = DriverManager.getConnection("jdbc:oracle:thin:@192.168.2.20:1521/EPM", "redmineviewer", "redmineviewer");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        // (redmineUserId,EPMUserId)
        Map<Integer,Integer> userMap = getUserMap();

        IssueManager issueManager = redmineManager.getIssueManager();
        List<Issue> results = null;
        try {
            results = issueManager.getIssues("461", null);
        } catch (RedmineException e) {
            e.printStackTrace();
        }

        for (Issue issue : results) {
            if (issue.getStatusId() == 39){
                Integer issueId = issue.getId();
                String subject = issue.getSubject();
                String[] split = subject.split("#@#");
                String EPMId = split[1];
                Integer assigneeId = issue.getAssigneeId();
                Integer planUser = userMap.getOrDefault(assigneeId, 0);
                Date dueDate = issue.getDueDate();
                java.sql.Date planDate = new java.sql.Date(dueDate.getTime());
                Float estimatedHours = issue.getEstimatedHours();
//                Collection<Attachment> attachments = issue.getAttachments();

                try {
                    // 更新 epm_problem 表
                    String sql;
                    sql = "UPDATE epmuser2.epm_problem SET STATUS = ?, PLAN_USER = ?, PLAN_DATE = ?, PLAN_TIME = ? WHERE ID = ?";
                    PreparedStatement sta = oracle.prepareStatement(sql);
                    sta.setInt(1,8);// 状态
                    sta.setInt(2,planUser);// 指派给
                    sta.setDate(3,planDate);// 计划完成时间
                    sta.setInt(4,estimatedHours.intValue());// 预计工时
                    sta.setString(5,EPMId);// id
                    sta.executeUpdate();
                    System.out.println("EPMId:" + EPMId + " 已完成,epm_problem 表状态更新成功");

                    // 插入 EPM_PROBLEM_DETAIL 表
                    // 先获取序列的值，因为后续操作需要使用
                    String idSql = "SELECT EPMUSER2.SQ_EPM_PROBLEM_DETAIL.nextval AS DID FROM dual";
                    PreparedStatement IdSta = oracle.prepareStatement(idSql);
                    ResultSet resultSet = IdSta.executeQuery();
                    resultSet.next();
                    int dId = resultSet.getInt("DID");
                    // 插入
                    sql = "INSERT into EPMUSER2.EPM_PROBLEM_DETAIL (ID,USER_ID,DESCRIPTION,PROBLEM_ID,PHASE_ID,NOTE,CREATED_AT,UPDATED_AT) " +
                            "values(?,?,?,?,?,?,SYSDATE,SYSDATE)";
                    PreparedStatement DSta = oracle.prepareStatement(sql);
                    DSta.setInt(1,dId);// id
                    DSta.setInt(2,planUser);// userid
                    DSta.setString(3,"");// 描述
                    DSta.setInt(4,Integer.parseInt(EPMId));// PROBLEM_ID
                    DSta.setInt(5,8);// PHASE_ID 阶段编号
                    DSta.setString(6,"");// NOTE
                    DSta.executeUpdate();
                    System.out.println("EPMId:" + EPMId + " EPM_PROBLEM_DETAIL 表成功插入完成阶段");

                    // 插入 EPM_PROBLEM_WORKTIME 表
                    sql = "INSERT INTO EPMUSER2.EPM_PROBLEM_WORKTIME (ID,DETAIL_ID,USER_ID,WORKTIME,AUDIT_WORKTIME,CREATED_AT,UPDATED_AT,PHASE_ID,PROBLEM_ID,IS_UPDATED,REASON,SOLUTION,RESOLVENT) " +
                            "values(EPMUSER2.SQ_EPM_PROBLEM_WORKTIME.nextval,?,?,?,?,SYSDATE,SYSDATE,?,?,0,?,?,null)";
                    PreparedStatement WSta = oracle.prepareStatement(sql);
                    WSta.setInt(1,dId);//dId
                    WSta.setInt(2,planUser);//userid
                    WSta.setFloat(3,estimatedHours.intValue());
                    WSta.setFloat(4,estimatedHours.intValue());

                    WSta.setInt(5,8);
                    WSta.setInt(6,Integer.parseInt(EPMId));
                    WSta.setString(7,"已完成");
                    WSta.setString(8,"已完成");
                    WSta.executeUpdate();
                    System.out.println("EPMId:" + EPMId + " EPM_PROBLEM_WORKTIME 表成功插入一条工时记录");


                    // 插入 EPM_PROBLEM_FILE 表 (因为redmine没有提供附件的物理存储地址，暂时不开发)
//                    for (Attachment attachment : attachments) {
//                        String fileName = attachment.getFileName();
//                        sql = "SELECT ID FROM EPMUSER2.EPM_PROBLEM_FILE WHERE PROBLEM_ID  = ? AND FILE_NAME = ?";
//                        PreparedStatement FSta = oracle.prepareStatement(sql);
//                        FSta.setInt(1,Integer.parseInt(EPMId));
//                        FSta.setString(2,fileName);
//                        ResultSet result = FSta.executeQuery();
//                        if (!result.next()){
//                            // 插入file
//                            sql = "INSERT into EPMUSER2.EPM_PROBLEM_FILE (ID,PROBLEM_ID,CONTENT,FILE_NAME,CREATED_AT,UPDATED_AT,KIND) " +
//                                    "values(EPMUSER2.SQ_EPM_PROBLEM_FILE.nextval,?,?,?,SYSDATE,SYSDATE,?)";
//                            PreparedStatement IFSta = oracle.prepareStatement(sql);
//                            IFSta.setInt(1,Integer.parseInt(EPMId));
//                            IFSta.setBlob(2,file2Blob("D:\\test\\" + fileName));// blob
//                            IFSta.setString(3,fileName);
//                            IFSta.setString(4,null);
//                            IFSta.executeUpdate();
//                            System.out.println("EPMId:" + EPMId + " EPM_PROBLEM_FILE 表成功插入");
//                        }
//                    }

                    // 将redmine任务改为关闭状态
                    issue.setTransport(transport);
                    issue.setStatusId(40).update();
                    System.out.println(issueId + "状态更改为关闭");

                } catch (Exception e) {
                    throw new RuntimeException(e);
                }

            }
        }

    }

    private Map<Integer,Integer> getUserMap() {
        // (emailName, redmine_user_id)
        HashMap<String, Integer> EPMUserMap = getEPMUserMap();
        HashMap<String, Integer> redmineUserMap = getRedmineUserMap();
        // (redmineUserId, EPMUserId)
        HashMap<Integer, Integer> userMap = new HashMap<>();

        Set<String> emailNameSets = redmineUserMap.keySet();
        for (String emailName : emailNameSets) {
            Integer redmineUserId = redmineUserMap.get(emailName);
            Integer EPMUserId = EPMUserMap.getOrDefault(emailName,0);
            if (EPMUserId != 0){
                userMap.put(redmineUserId,EPMUserId);
            }
        }
        return userMap;
    }

    private HashMap<String, Integer> getRedmineUserMap() {
        // 从redmine数据库中取到mail和id的映射
        Connection mysqlConn = null;
        ResultSet sqlResultSet = null;
        try {
            mysqlConn = DriverManager.getConnection("jdbc:mysql://192.168.8.10:3306/redmine_409", "redmine", "rm@jhhs_2022");
            PreparedStatement sql = mysqlConn.prepareStatement("select user_id,address from email_addresses");
            sql.setFetchSize(300);
            sqlResultSet = sql.executeQuery();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        // (emailName, redmine_user_id)
        HashMap<String, Integer> userMap = new HashMap<>();
        try {
            while (sqlResultSet.next()) {
                String email = sqlResultSet.getString("address");
                String emailName = email.substring(0, email.indexOf("@"));
                int user_id = sqlResultSet.getInt("user_id");
                userMap.put(emailName, user_id);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                mysqlConn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return userMap;
    }

    private HashMap<String, Integer> getEPMUserMap() {
        // 从EPM数据库中取到mail和id的映射
        Connection mysqlConn = null;
        ResultSet sqlResultSet = null;
        try {
            mysqlConn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.2.20:1521/EPM", "redmineviewer", "redmineviewer");
            PreparedStatement sql = mysqlConn.prepareStatement("select ID,MAIL from EPMUSER2.EPM_VIEW_USER");
            sql.setFetchSize(300);
            sqlResultSet = sql.executeQuery();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        // (emailName, epm_user_id)
        HashMap<String, Integer> userMap = new HashMap<>();
        try {
            while (sqlResultSet.next()) {
                String email = sqlResultSet.getString("MAIL");
                String emailName = email.substring(0, email.indexOf("@"));
                int user_id = sqlResultSet.getInt("ID");
                userMap.put(emailName, user_id);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                mysqlConn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return userMap;
    }

    public static Blob file2Blob(String inFile) {

        try {
            byte[] result = file2Byte(inFile);
            BLOB blob = BLOB.empty_lob();
            blob.setBytes(1, result);
            return blob;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public static byte[] file2Byte(String inFile) {
        InputStream in = null;
        try {
            in = Files.newInputStream(new File(inFile).toPath());
            int len = in.available();
            byte[] result = new byte[len];
            in.read(result);
            return result;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}
